import math
import pprint
def conv_to_minutes(timestamp, offset=None):
  x = "(cast(substr(%s, 1, 2) as int)*60 + cast(substr(%s, 3, 2) as int))" % (
      timestamp, timestamp)
  if offset:
    x = "%s+%s" % (x, offset)
  return x

def is_overlapping(x1, x2, y1, y2):
  """
  Compiles the SQL predicate that returns true iff [x1,x2) overlaps with [y1,y2)
  """
  return "(%(y1)s < %(x2)s and %(y2)s > %(x1)s)" % dict(
         x1=x1, x2=x2, y1=y1, y2=y2)

  #return ("((%(x1)s <= %(y1)s and %(y1)s < %(x2)s) " + \
  #       "or (%(x1)s < %(y2)s and %(y2)s < %(x2)s) "  + \
  #       "or (%(y1)s <= %(x1)s and %(x1)s < %(y2)s) " + \
  #       "or (%(y1)s < %(x2)s and %(x2)s < %(y2)s))") % dict(
  #       x1=x1, x2=x2, y1=y1, y2=y2)

def sql_freerooms(dur, search=None, timeonly=False):
  """ builds the parametric SQL query to fetch the free rooms
      of given duration, and with the where conditions.
      SQL is parameterized by semester
      where: list( (attr, pattern) )
  """
  n = int(math.ceil(dur / 30.))
  _from = "FreeRoom F0"
  j = 0
  for j in range(1, n):
    i = j - 1
    _from += """ join FreeRoom F%(j)d on 
                (F%(i)d.day = F%(j)d.day and
                 F%(i)d.location = F%(j)d.location and
                 F%(i)d.semester = F%(j)d.semester and
                 F%(i)d.btimemin+F%(i)d.duration = F%(j)d.btimemin) """ % dict(
                 i=i, j=j)
  _where = "where F0.semester=?"
  pprint.pprint(search)
  if search:
    for (attr, op, val) in search:
      if not type(val) == int: val = "'%s'" % val
      _where += " and F0.%s %s %s" % (attr, op, val)

  if timeonly:
    return """select F0.btime, F0.day,
              F%(j)d.btimemin+F%(j)d.duration-F0.btimemin as duration,
              count(distinct F0.location) as numlocs
              from %(_from)s %(where)s
              group by F0.btime, F0.day, duration""" % dict(
              _from=_from, j=j, where=_where)

  else:
    return """SELECT distinct F0.campus, F0.location, F0.btime, F0.day,
              F%(j)d.btimemin+F%(j)d.duration-F0.btimemin as duration
              from %(_from)s %(where)s""" % dict(_from=_from, j=j, where=_where)


